﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Common;

namespace DAL
{
    public class KeyDAL : AbstractDAL<Key>
    {
        static readonly string INSERT = "insert into Key values(null,@Name);select last_insert_rowid() newid;";
        static readonly string UPDATE = "update Key set Name=@Name where id=@ID";
        static readonly string DELETE = "delete from Key where id=@ID";
        static readonly string SELECT = "select * from  Key where id=@ID";
        static readonly string SELECT_ALL = "select * from  Key";
        static readonly string SELECT_ALL_KEYCATEGORY_VIEW = "select a.id,a.Name,b.cid,c.[Name] as CategoryName from key a left join keyscategory b on a.[ID]=b.[kID] left join category c on b.cid=c.id";
        static readonly string SELECT_SeachKeyByName = "select * from  Key where Name like @Name";

        public List<KeyCategoryView> GetAllKeyCategoryView()
        {
            List<KeyCategoryView> list = new List<KeyCategoryView>();
            DbDataReader reader = null;
            try
            {
                reader = SqlHelper.GetCommand(SELECT_ALL_KEYCATEGORY_VIEW).ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetKeyCategoryViewModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        protected override string InsertCommand
        {
            get
            {
                return INSERT;
            }
        }

        protected override string UpdateCommand
        {
            get { return UPDATE; }
        }

        protected override string DeleteCommand
        {
            get { return DELETE; }
        }

        protected override string SelectCommand
        {
            get { return SELECT; }
        }

        protected override string SelectAllCommand
        {
            get { return SELECT_ALL; }
        }

        protected override DbParameter[] GetInsertParameter(Key model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@Name", model.Name) };
        }

        protected override void SyncID(Key model, object scalar)
        {
            model.ID = (long)scalar;
        }

        protected override DbParameter[] GetUpdateParameter(Key model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID), SqlHelper.CreateParameter("@Name", model.Name) };
        }

        protected override DbParameter[] GetDeleteParameter(Key model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID) };
        }

        protected override DbParameter[] GetSeleteParameter(long id)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", id) };
        }

        protected override Key GetModel(DbDataReader reader)
        {
            Key entity = new Key();
            entity.ID = (long)reader["ID"];
            entity.Name = (string)reader["Name"];
            return entity;
        }

        protected KeyCategoryView GetKeyCategoryViewModel(DbDataReader reader)
        {
            KeyCategoryView entity = new KeyCategoryView { KeyItem = new Key() };
            entity.KeyItem.ID = (long)reader["ID"];
            entity.KeyItem.Name = (string)reader["Name"];
            if (reader.IsDBNull(2))
            {
                return entity;
            }
            entity.CategoryItem = new Category();
            entity.CategoryItem.ID = (long)reader["CID"];
            if (!reader.IsDBNull(3))
            {
                entity.CategoryItem.Name = (string)reader["CategoryName"];
            }
            return entity;
        }

        public List<Key> SeachKeyByName(string name, List<long> filterID = null)
        {
            List<Key> list = new List<Key>();
            DbParameter parameter = SqlHelper.CreateParameter("@Name", "%" + name + "%");
            DbCommand command = null;
            DbDataReader reader = null;
            string sql = SELECT_SeachKeyByName;
            if (filterID != null)
            {
                sql = sql + " and id in(" + string.Join(",", filterID) + ")";
            }

            try
            {
                command = SqlHelper.GetCommand(SELECT_SeachKeyByName);
                command.Parameters.Add(parameter);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            catch
            {
                SqlHelper.Close(command, reader);
            }
            return list;
        }
    }
}
